package homework2;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;

import org.junit.Test;

//批量处理插入修改删除数据（PreparedStatement/Statement两种都需实现）

public class TestShiwu {

	public static void main(String[] args) throws SQLException {
		new TestShiwu().test4();
	}

	// 事务
	// 把连续执行的一系列sql（主DML）语句视为一个整体
	// 开启事务 1-取消自动提交事务 conn.setAutoCommit(false);
	// 提交事务 2-使用commit主动提交事务来更新数据
	// 回滚事务 3-使用rollback将数据回滚到开始事务时

	public void test() {
		String s1 = "update user set money=money+10 where rowId=1";
		String s3 = "update user set money=money+10 where rowId=1";
		String s2 = "update user set money=money+10 where rowId=1";
		Connection conn = null;
		PreparedStatement ps = null;
		conn = JDBCUtil.getConnection();
		try {
			ps = conn.prepareStatement(s1);
			ps.executeUpdate();
			ps = conn.prepareStatement(s2);
			ps.executeUpdate();
			ps = conn.prepareStatement(s3);
			ps.executeUpdate();// 分别执行多个sql语句，不视为一个整体
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JDBCUtil.close(conn, ps);
		}
	}

	public void test1() {
		String s1 = "update user set money=money+10 where rowId=1";
		String s3 = "update user set money=money+10 where rowId=1";
		String s2 = "update user set money=money+10 where rowId=1";
		Connection conn = null;
		PreparedStatement ps = null;

		try {
			conn = JDBCUtil.getConnection();
			conn.setAutoCommit(false);// 取消自动提交--开启事务
			ps = conn.prepareStatement(s1);
			ps.executeUpdate();
			ps = conn.prepareStatement(s2);
			ps.executeUpdate();
			ps = conn.prepareStatement(s3);
			ps.executeUpdate();
			conn.commit();// 提交事务
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			try {
				conn.rollback();// 若发生异常，对事物回滚，回到事物开启的位置
				// 多个sql语句即使有正确执行的，也不会提交到数据库（保证数据库里的数据是正确的），视为一个整体-事务
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			} // 回滚事务
		} finally {
			JDBCUtil.close(conn, ps);
		}
	}

	// 批量增加处理PreparedStatement
	public void test2() throws SQLException {
		Connection conn = JDBCUtil.getConnection();
		String s1 = "insert into user（userName）values(?)";
		PreparedStatement ps = conn.prepareStatement(s1);
		conn.setAutoCommit(false);
		long start = System.currentTimeMillis();
		for (int i = 1; i <= 1000; i++) {
			ps.setString(1, "王五"+i);
			ps.addBatch();// 添加批量处理的数据
		}
		int[] result = ps.executeBatch();// 执行批量处理的过程，执行多个语句影响的条数
		ps.clearBatch();// 清空批量处理的数据
		conn.commit();
		JDBCUtil.close(conn, ps);
		long end = System.currentTimeMillis();
		System.out.println(end - start);// 时长
	}

	// 标准的sql语句insert--Statement
	public void test3() throws SQLException {
		Connection conn = JDBCUtil.getConnection();

		Statement st = conn.createStatement();
		conn.setAutoCommit(false);
		for (int i = 1; i <= 1000; i++) {
			String s1 = "insert into user(userName) values('王五'"+i+")";
			st.addBatch(s1);// 添加批量处理的数据
		}
		int[] result = st.executeBatch();// 执行批量处理的过程，执行多个语句影响的条数
		st.clearBatch();// 清空批量处理的数据
		conn.commit();
		JDBCUtil.close(conn, st);
	}

	// 练习
	// 预处理 批量修改
	@Test
	public void test4() throws SQLException {
		Connection conn = JDBCUtil.getConnection();
		//String s1 = "update student set s_name=?,s_sex=? where s_id=?";
		String s1 = "update person set name=?,gendar=? where id=?";
		PreparedStatement ps = conn.prepareStatement(s1);
		conn.setAutoCommit(false);		
		  long start=System.currentTimeMillis(); 
		  for(int i=11;i<=13;i++) {
		  System.out.println("张三三"+i); 
		  ps.setString(1, "张三三 "+i);
		  ps.setString(2, "男"+i);
		  ps.setInt(3, i);
		  ps.addBatch();//添加批量处理的数据
		  	} 
		  int[] result=ps.executeBatch();//执行批量处理的过程，执行多个语句影响的条数 ps.clearBatch();//清空批量处理的数据
		 
		 conn.commit();
		JDBCUtil.close(conn, ps);
		long end = System.currentTimeMillis();
		System.out.println(end - start);// 时长
	}

	// 标准的sql语句-update
	// @Test
	public void test5() throws SQLException {
		Connection conn = JDBCUtil.getConnection();
		Statement st = conn.createStatement();
		conn.setAutoCommit(false);
		for (int i = 1; i <= 10; i++) {
//			String s1 = "update student set s_name='李思思 " + i + "',s_sex='女"+i+"' where s_id="+i+"";
			String s1 = "update person set name='李思思" + i + "',age=age+"+i+" where id="+i+"";
			st.addBatch(s1);// 添加批量处理的数据
		}
		int[] result = st.executeBatch();// 执行批量处理的过程，执行多个语句影响的条数
		st.clearBatch();// 清空批量处理的数据
		conn.commit();
		JDBCUtil.close(conn, st);
	}
	// PreparedStatement-update
	//@Test
	public void test6() throws SQLException {
		long start=System.currentTimeMillis(); 
		Connection conn = JDBCUtil.getConnection();
		String s1 = "update student set s_name=?,s_sex=?";
		PreparedStatement ps = null;
		conn = JDBCUtil.getConnection();
		conn.setAutoCommit(false);	
		ps = conn.prepareStatement(s1); 
		ps.setString(1, "娃哈哈");
		ps.setString(2, "男");
		ps.executeUpdate();  		 
		conn.commit();
		JDBCUtil.close(conn, ps);
		long end = System.currentTimeMillis();
		System.out.println(end - start);// 时长
	}
	
	//PreparedStatement-delete
	public void delete() throws SQLException {
		Connection conn = JDBCUtil.getConnection();
		String sql = "delete from student where s_id=?";
		PreparedStatement ps = conn.prepareStatement(sql);
		conn.setAutoCommit(false);
		for (int i = 1; i <= 5; i++) {
			ps.setInt(1, i);
			ps.addBatch();
		}
		int[] result = ps.executeBatch();
		ps.clearBatch();
		conn.commit();
		JDBCUtil.close(conn, ps);
	}
	//标准sql语句--删除
	public void delect() throws SQLException {
		Connection conn = JDBCUtil.getConnection();
		Statement st = conn.createStatement();
		conn.setAutoCommit(false);// 事物开启
		for (int i = 1; i <= 5; i++) {
			String sql = "delete from student where s_id=" + i;
			System.out.println(sql);
			st.addBatch(sql);
		}
		int[] result = st.executeBatch();
		st.clearBatch();
		conn.commit();
		JDBCUtil.close(conn, st);

	}


}
